package DocumentProcessing;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * Created by doris on 2016/10/26.
 */
public class DocumentProcessing {
    private String srcImagePath = null;

    public DocumentProcessing(String name) {
        this.srcImagePath = name;
    }

    //输出所有单元格内容到控制台
    public void xlsxRead() throws IOException, InvalidFormatException {
        FileInputStream inout = new FileInputStream(srcImagePath);
        Workbook workbook = WorkbookFactory.create(inout);
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            for (Cell cell : row) {
                System.out.print(cell.toString() + " ");
            }
            System.out.println();
        }
        inout.close();
    }

    //修改单元格格式
    public void setCellFromate(int setRow, int setCell, String value,String outPath) throws IOException, InvalidFormatException {
        FileInputStream input = new FileInputStream(srcImagePath);
        HSSFWorkbook workbook = new HSSFWorkbook(input);
        HSSFSheet sheet = workbook.getSheetAt(0);

        HSSFCell myCell = sheet.getRow(setRow).getCell(setCell);
        myCell.setCellValue(value);

        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.RED.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        style.setFont(font);

        myCell.setCellStyle(style);

        input.close();
        FileOutputStream outputStream = new FileOutputStream(outPath);
        workbook.write(outputStream);
        outputStream.close();
    }

    // 合并单元格
    public void mergeCells(int startRow, int startCell, int endRow, int endCell, String value,String outPath) throws IOException {
        FileInputStream input = new FileInputStream(srcImagePath);
        HSSFWorkbook workbook = new HSSFWorkbook(input);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFCell mycell = sheet.getRow(startRow).getCell(startCell);
        mycell.setCellValue(value);
        CellRangeAddress region = null;
        region = new CellRangeAddress(startRow, endRow, startCell, endCell);
        sheet.addMergedRegion(region);
        input.close();
        FileOutputStream outputStream = new FileOutputStream(outPath);
        workbook.write(outputStream);
        outputStream.close();
    }

    // 改变对齐
    public void ChangAllAlignment(String outPath) throws IOException {
        FileInputStream input = new FileInputStream(srcImagePath);
        HSSFWorkbook workbook = new HSSFWorkbook(input);
        HSSFSheet sheet = workbook.getSheetAt(0);

        for (Row row : sheet) {
            for (Cell cell : row) {
                HSSFCell myCell = sheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex());
                HSSFCellStyle style = workbook.createCellStyle();
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
                style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                style.setWrapText(true);//自动换行
                myCell.setCellStyle(style);
            }
        }

        input.close();
        FileOutputStream outputStream = new FileOutputStream(outPath);
        workbook.write(outputStream);
        outputStream.close();
    }

    //公式加法
    public void sum(String start,String end,String outPath) throws IOException {
        FileInputStream input = new FileInputStream(srcImagePath);
        HSSFWorkbook workbook = new HSSFWorkbook(input);
        HSSFSheet sheet = workbook.getSheetAt(0);
        String sum="sum("+start+":"+end+")";
        sheet.getRow(0).createCell(12).setCellFormula(sum);

        input.close();
        FileOutputStream outputStream = new FileOutputStream(outPath);
        workbook.write(outputStream);
        outputStream.close();
    }
}
